﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Models;
using System.Reflection;
using SqlSugar;

namespace DAL
{
    public class D_Student
    {

        public bool Update_groupid(List<Student> students, int group_id)
        {
            int rows=0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Student set ");
            strSql.Append("group_id=@group_id where stu_id=@stu_id");

            foreach(Student stu in students)
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@group_id", SqlDbType.Int,10),
                    new SqlParameter("@stu_id", SqlDbType.Int,11),
                };
                parameters[0].Value = group_id;
                parameters[1].Value = stu.stu_id;

                rows += SqlDbHelper.ExecuteNonQuery(strSql.ToString(), CommandType.Text, parameters);
            }
            return rows == students.Count ? true : false;
        }


        public bool Update_groupid_suger(Student student)
        {
            var rows = Sugar_helper.Db.Updateable<Student>(student).Where(stu => stu.stu_id == student.stu_id).ExecuteCommand();
            return rows == 0 ? true : false;
        }

        public bool save_List(List<Student> students)
        {
            var count = Sugar_helper.Db.Storageable<Student>(students).WhereColumns(stu => new{stu.stu_no}).ExecuteCommand();
            return count == students.Count ? true : false;
        }

        public int release_groupid(Stu_group stu_Group)//更新该组中所有学生对应的学生表里的信息
        {
            string strSql = $"update student set group_id=-1, is_gleader=-1 where group_id={stu_Group.group_id}";
            var rows = Sugar_helper.Db.Ado.ExecuteCommand(strSql);
            return rows;
        }

        public List<Student> GetList(int grade, int config_id)
        {
            var list = Sugar_helper.Db.Queryable<Student>().Where(Student=>Student.grade==grade && Student.major_id==config_id).ToList();
            return list;
        }

        public List<Student> GetList()
        {
            var list = Sugar_helper.Db.Queryable<Student>().ToList();
            return list;
        }

        public List<Student> GetPage(int Page_idx, int Page_size, ref int totalCount)
        {
            return Sugar_helper.Db.Queryable<Student>().ToPageList(Page_idx, Page_size, ref totalCount);
        }

        public List<Student> GetPage_storePro(int Page_idx, int Page_size, ref int totalCount)
        {
            var paras = new List<SugarParameter>();
            paras.Add(new SugarParameter("@PageNumber", Page_idx));
            paras.Add(new SugarParameter("@PageSize", Page_size));
            paras.Add(new SugarParameter("@TotalCount", totalCount));
            paras[2].Direction = ParameterDirection.Output;
            var res = Sugar_helper.Db.Ado.UseStoredProcedure().SqlQuery<Student>("GetPagedData",paras); 
            totalCount = (int)paras[2].Value;
            return res;
        }


        public List<Student> GetList(Models.Stu_group group)
        {
            var list = Sugar_helper.Db.Queryable<Student>().Where(stu=>stu.group_id==group.group_id).ToList();
            return list;
        }

        public List<Student> GetList_wo_Group()
        {
            var list = Sugar_helper.Db.Queryable<Student>().Where(stu=>stu.group_id==-1).ToList();
            return list;
        }
        

        public bool Login_sugar(Student m_stu)
        {
            var student = Sugar_helper.Db.Queryable<Student>().Where(stu=>stu.stu_no==m_stu.stu_no&&stu.pwd==m_stu.pwd).ToList();
            if (student.Count == 0) 
                return false;

            log_info.Server_Time = Sugar_helper.Db.GetDate();

            log_info.student = student[0];

            if (student[0].group_id != -1)
            {
                var stu_group = Sugar_helper.Db.Queryable<Stu_group>().Where(g=>g.group_id==student[0].group_id).ToList().First();
                log_info.stu_Group = stu_group;
            }

            //查询Sys_config表，获取所有系统配置记录，并将结果存储在sys_config变量中。
            var sys_config = Sugar_helper.Db.Queryable<Sys_config>().ToList();
            if (sys_config.Count > 0)
                //用了查询到的第一条记录，拿到了一组的上限人数
                log_info.Sys_Config.group_members = sys_config[0].group_members;
            
            return true;
        }

        public bool Add(List<Student> students)
        {
            var res = Sugar_helper.Db.Storageable(students).WhereColumns(stu => new { stu.stu_no }).ExecuteCommand();
            return res == students.Count ? true : false;
        }

        public bool Del(List<Student> students)
        {
            var res = Sugar_helper.Db.Deleteable(students).ExecuteCommand();
            return res == students.Count ? true : false;
        }



        //   public bool ResetGroupLeaders(int groupId, int leaderId)//提交队伍时修改is_gleader
        //   {
        //       string strSql = $@"
        //      UPDATE Student 
        //      SET is_gleader = -1 
        //      WHERE group_id = {groupId} AND stu_id != {leaderId}";
        //
        //       int rowsAffected = Sugar_helper.Db.Ado.ExecuteCommand(strSql);
        //       return rowsAffected > 0;
        //   }
        //
        //   public bool ResetGroupLeadersAfterDisband(int groupId, int leaderId)
        //   {
        //       string strSql = $@"
        //   UPDATE Student
        //   SET is_gleader = -1
        //   WHERE group_id = {groupId} AND stu_id != {leaderId}";
        //
        //       int rowsAffected = Sugar_helper.Db.Ado.ExecuteCommand(strSql);
        //       return rowsAffected > 0;
        //   }



    }
}
